package javacommon.util;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.ResultSetMetaData;
public class ConnectionManager {
	private String driver = "com.mysql.jdbc.Driver";

	private String url = "jdbc:mysql://localhost:3306/zhaogr";

	private String dbuser = "zhaogr";

	private String dbpwd = "nail@126.com";

	private static String aDefaultDataSource = "longskyDs";

	private static DataSource ds = null;

	private static Hashtable hsTable = new Hashtable();
	public ConnectionManager(){
		//createDataSource(aDefaultDataSource);
	}
	public ConnectionManager(String driver,String url,String dbuser,String dbpwd){
		this.driver=driver;
		this.url=url;
		this.dbuser=dbuser;
		this.dbpwd=dbpwd;
	}

	//	ͨ��JNDI���DataSource
	private boolean createDataSource(String dsName) {
		if (ds != null)
			return true;
		try {
			hsTable.put("java.naming.factory.initial",
					"weblogic.jndi.WLInitialContextFactory");
			Context ctx = new InitialContext(hsTable);
			ds = (DataSource) ctx.lookup(dsName);
			return true;
		} catch (Exception e) {
		}
		return false;
	}
	//ͨ��ds���connection
	/*public Connection dbConnect2() {
		Connection conn = null;
		if (ds != null) {
			try {
				conn = (Connection)ds.getConnection();
			} catch (Exception e) {
				LogServlet.error("getConnection�����쳣: " + e.getMessage());
			}
		} else
			LogServlet.error("��� DataSource --" + aDefaultDataSource
					+ " Failed!");
		return conn;
	}*/
	//ͨ��drivermanager���connection
	public Connection dbConnect(){
		Connection conn = null;
		try{
			Class.forName(driver);
			conn=(Connection)DriverManager.getConnection(url,dbuser,dbpwd);
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
		return conn;
	}
	public boolean closeConnection(Connection conn) {
		boolean bRetn = true;
		if (conn != null)
			try {
				conn.close();
			} catch (Exception e) {
				bRetn = false;
				//LogServlet.error("Close connection exception:"+ e.getMessage());
			}
		return bRetn;
	}
	
	public  int doInsert(Connection conn,String strsql) throws SQLException{
		int itn=0;
		PreparedStatement pstmt=null;
		//String strsql=null;
		try{
			//strsql="insert into temp (name,address,amount,city,des,type) values('"+map.get("name")+"','"+map.get("address")+"',"+map.get("count")+",'"+map.get("city")+"','"+map.get("desc")+"',"+type+")";
			pstmt=(PreparedStatement) conn.prepareStatement(strsql);
			itn=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
		finally{
			if(pstmt!=null)
				pstmt.close();
		}
		return itn;
	}
	
	public  int doInsertCity(Connection conn,Map map) throws SQLException{
		int itn=0;
		PreparedStatement pstmt=null;
		String strsql=null;
		try{
			strsql="insert into city (cityId,parentCityId,name,code) values("+map.get("id")+","+map.get("pid")+",'"+map.get("name")+"','"+map.get("code")+"')";
			pstmt=(PreparedStatement) conn.prepareStatement(strsql);
			itn=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return -1;
		}
		finally{
			if(pstmt!=null)
				pstmt.close();
		}
		return itn;
	}
	
	public  int doInsertPort(Connection conn,String chn,String en,Long id) throws SQLException{
		int itn=0;
		PreparedStatement pstmt=null;
		String strsql=null;
		try{
			strsql="insert into ports (countryId,portNameEn,portNameChn) values("+id+",'"+en+"','"+chn+"')";
			pstmt=(PreparedStatement) conn.prepareStatement(strsql);
			itn=pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
			return -1;
			
		}
		finally{
			if(pstmt!=null)
				pstmt.close();
		}
		return itn;
	}
	
	public List<Map<String,Object>> doQuery(Connection conn,String sql)throws Exception{
		List<Map<String,Object>> list = new ArrayList();
		PreparedStatement pstmt=null;
		try{
			pstmt=(PreparedStatement) conn.prepareStatement(sql);
			ResultSet rs = (ResultSet)pstmt.executeQuery();
			ResultSetMetaData meta = (ResultSetMetaData)rs.getMetaData();
			int columnCount  = meta.getColumnCount();
			while (rs.next()){
				Map<String,Object> map = new HashMap<String,Object>();
				for(int i=1;i<=columnCount;i++){
					String columnName = meta.getColumnName(i);
					map.put(columnName, rs.getObject(columnName));
				}
				list.add(map);
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return list;
			
		}
		return list;
	}

	public static void main(String[] args){
		try {
			String[] title = {"名称","地址","场地数量","城市","说明"};
		ConnectionManager cm=new ConnectionManager();
		Connection conn=cm.dbConnect();
		String sql = "select name,address,amount,city,des,type from temp";
		List<Map<String,Object>> list = cm.doQuery(conn, sql);
			//FileUtils.getDataFromTxt();
		
		for(Map map :list){
			cm.doInsertCity(conn, map);
		}	
		
		/*List allList = new ArrayList();
		for(int i=1;i<13;i++){
			//tennis
			String filePath = "http://www.xbooking.com/activity_search.php?category=ping-pong&province=0&city=0&gymname=&Input=%CB%D1%CB%F7%B3%A1%B5%D8&page="+i;
			ParseHTML parserHtml = new ParseHTML();
			List list = parserHtml.parse(filePath);
			//System.out.println(list.size());
			for (Map map : list) {
					//cm.doInsert(conn, map);
			}
			allList.addAll(list);
		}
		
		Workbook wb =ExcelUtils.createExcel("ping-pong", title, allList, 5);
    	FileOutputStream fileOut = new FileOutputStream("d:\\ping-pong.xls");   
        wb.write(fileOut);   
        fileOut.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}*/
	}catch(Exception e){
		e.printStackTrace();
	}
	}
}